﻿using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Frunit.Model.Ent;
using OMG.Fill.Frunit.Services;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Web;
using System.Web.Http;
namespace OMG.Fill.Frunit.Model.UI.Controllers
{
    public class FruTecPromotionController : ApiController
    {
        FruTecPromotionServices fruTecPromotionServices = new FruTecPromotionServices();

        //[ApiActionAuthorize("获取技术推广人员情况表T_FruTecPromotion分页列表对象操作")]
        //[ApiActionException]
        public Pager<List<FruTecPromotionEntity>> PostPageList(FruTecPromotionQuery query)
        {
            return fruTecPromotionServices.GetPageList(query);
        }
        //[ApiActionAuthorize("新增技术推广人员情况表T_FruTecPromotion指定对象操作")]
        //[ApiActionException]
        public ResResult<int> Post(FruTecPromotionEntity model)
        {
            return fruTecPromotionServices.AddInfo(model);
        }
        //[ApiActionAuthorize("修改技术推广人员情况表T_FruTecPromotion指定对象操作")]
        //[ApiActionException]
        public ResResult<int> Put(FruTecPromotionEntity model)
        {
            return fruTecPromotionServices.UpdateInfo(model);
        }
        //[ApiActionAuthorize("删除技术推广人员情况表T_FruTecPromotion指定对象操作")]
        //[ApiActionException]
        public ResResult<int> Delete(string id)
        {
            return fruTecPromotionServices.DeleteInfo(id);
        }
        //[ApiActionAuthorize("获取技术推广人员情况表T_FruTecPromotion指定对象操作")]
        //[ApiActionException]
        //public ResResult<FruTecPromotionEntity> Get(string id)
        //{
        //    return fruTecPromotionServices.GetModel(id);
        //}
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream Method(VegNowModel model)
        {
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\FruDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("FruTecPromotion") as HSSFSheet;//读取模板所在sheet位置
            ResResult<FruTecPromotionViewModel> pagelist = fruTecPromotionServices.GetModel(model);//数据获取

            HSSFRow row = (HSSFRow)sheet.GetRow(4);//读取模板第5行
            var style = row.GetCell(2).CellStyle;//读取模板第5行第一单元格样式并保存
            row.GetCell(2).CellStyle = style;//对第二行第一单元格设置原来样式

            HSSFRow datarow1 = (HSSFRow)sheet.GetRow(4);
            datarow1.GetCell(2).SetCellValue(pagelist.model.InstituNum == null ? "" : pagelist.model.InstituNum.ToString());
            datarow1.GetCell(2).CellStyle = style;

            HSSFRow datarow2 = (HSSFRow)sheet.GetRow(5);
            datarow2.GetCell(2).SetCellValue(pagelist.model.InstituNum_City == null ? "" : pagelist.model.InstituNum_City.ToString());
            datarow2.GetCell(2).CellStyle = style;

            HSSFRow datarow3 = (HSSFRow)sheet.GetRow(6);
            datarow3.GetCell(2).SetCellValue(pagelist.model.InstituNum_Area == null ? "" : pagelist.model.InstituNum_Area.ToString());
            datarow3.GetCell(2).CellStyle = style;

            HSSFRow datarow4 = (HSSFRow)sheet.GetRow(7);
            datarow4.GetCell(2).SetCellValue(pagelist.model.InstituNum_town == null ? "" : pagelist.model.InstituNum_town.ToString());
            datarow4.GetCell(2).CellStyle = style;

            HSSFRow datarow5 = (HSSFRow)sheet.GetRow(8);
            datarow5.GetCell(2).SetCellValue(pagelist.model.PeopleNum == null ? "" : pagelist.model.PeopleNum.ToString());
            datarow5.GetCell(2).CellStyle = style;

            HSSFRow datarow6 = (HSSFRow)sheet.GetRow(9);
            datarow6.GetCell(2).SetCellValue(pagelist.model.PeopleNum_County == null ? "" : pagelist.model.PeopleNum_County.ToString());
            datarow6.GetCell(2).CellStyle = style;

            HSSFRow datarow7 = (HSSFRow)sheet.GetRow(10);
            datarow7.GetCell(2).SetCellValue(pagelist.model.PeopleNum_City == null ? "" : pagelist.model.PeopleNum_City.ToString());
            datarow7.GetCell(2).CellStyle = style;

            HSSFRow datarow8 = (HSSFRow)sheet.GetRow(11);
            datarow8.GetCell(2).SetCellValue(pagelist.model.PeopleNum_man == null ? "" : pagelist.model.PeopleNum_man.ToString());
            datarow8.GetCell(2).CellStyle = style;

            HSSFRow datarow9 = (HSSFRow)sheet.GetRow(12);
            datarow9.GetCell(2).SetCellValue(pagelist.model.PeopleNum_woman == null ? "" : pagelist.model.PeopleNum_woman.ToString());
            datarow9.GetCell(2).CellStyle = style;

            HSSFRow datarow10 = (HSSFRow)sheet.GetRow(14);
            datarow10.GetCell(2).SetCellValue(pagelist.model.PeopleNum_age1 == null ? "" : pagelist.model.PeopleNum_age1.ToString());
            datarow10.GetCell(2).CellStyle = style;

            HSSFRow datarow11 = (HSSFRow)sheet.GetRow(15);
            datarow11.GetCell(2).SetCellValue(pagelist.model.PeopleNum_age2 == null ? "" : pagelist.model.PeopleNum_age2.ToString());
            datarow11.GetCell(2).CellStyle = style;

            HSSFRow datarow12 = (HSSFRow)sheet.GetRow(16);
            datarow12.GetCell(2).SetCellValue(pagelist.model.PeopleNum_age3 == null ? "" : pagelist.model.PeopleNum_age3.ToString());
            datarow12.GetCell(2).CellStyle = style;

            HSSFRow datarow13 = (HSSFRow)sheet.GetRow(17);
            datarow13.GetCell(2).SetCellValue(pagelist.model.PeopleNum_age4 == null ? "" : pagelist.model.PeopleNum_age4.ToString());
            datarow13.GetCell(2).CellStyle = style;

            HSSFRow datarow14 = (HSSFRow)sheet.GetRow(19);
            datarow14.GetCell(2).SetCellValue(pagelist.model.PeopleNum_xlss == null ? "" : pagelist.model.PeopleNum_xlss.ToString());
            datarow14.GetCell(2).CellStyle = style;

            HSSFRow datarow15 = (HSSFRow)sheet.GetRow(20);
            datarow15.GetCell(2).SetCellValue(pagelist.model.PeopleNum_xlbk == null ? "" : pagelist.model.PeopleNum_xlbk.ToString());
            datarow15.GetCell(2).CellStyle = style;

            HSSFRow datarow16 = (HSSFRow)sheet.GetRow(21);
            datarow16.GetCell(2).SetCellValue(pagelist.model.PeopleNum_xldz == null ? "" : pagelist.model.PeopleNum_xldz.ToString());
            datarow16.GetCell(2).CellStyle = style;

            HSSFRow datarow17 = (HSSFRow)sheet.GetRow(22);
            datarow17.GetCell(2).SetCellValue(pagelist.model.PeopleNum_xlzz == null ? "" : pagelist.model.PeopleNum_xlzz.ToString());
            datarow17.GetCell(2).CellStyle = style;

            HSSFRow datarow18 = (HSSFRow)sheet.GetRow(23);
            datarow18.GetCell(2).SetCellValue(pagelist.model.PeopleNum_xlqt == null ? "" : pagelist.model.PeopleNum_xlqt.ToString());
            datarow18.GetCell(2).CellStyle = style;

            HSSFRow datarow19 = (HSSFRow)sheet.GetRow(25);
            datarow19.GetCell(2).SetCellValue(pagelist.model.PeopleNum_zczgj == null ? "" : pagelist.model.PeopleNum_zczgj.ToString());
            datarow19.GetCell(2).CellStyle = style;

            HSSFRow datarow20 = (HSSFRow)sheet.GetRow(26);
            datarow20.GetCell(2).SetCellValue(pagelist.model.PeopleNum_zcfgj == null ? "" : pagelist.model.PeopleNum_zcfgj.ToString());
            datarow20.GetCell(2).CellStyle = style;

            HSSFRow datarow21 = (HSSFRow)sheet.GetRow(27);
            datarow21.GetCell(2).SetCellValue(pagelist.model.PeopleNum_zczj == null ? "" : pagelist.model.PeopleNum_zczj.ToString());
            datarow21.GetCell(2).CellStyle = style;

            HSSFRow datarow22 = (HSSFRow)sheet.GetRow(28);
            datarow22.GetCell(2).SetCellValue(pagelist.model.PeopleNum_zccj == null ? "" : pagelist.model.PeopleNum_zccj.ToString());
            datarow22.GetCell(2).CellStyle = style;

            HSSFRow datarow23 = (HSSFRow)sheet.GetRow(29);
            datarow23.GetCell(2).SetCellValue(pagelist.model.PeopleNum_zcqt == null ? "" : pagelist.model.PeopleNum_zcqt.ToString());
            datarow23.GetCell(2).CellStyle = style;

            /* HSSFRow datarow = (HSSFRow)sheet.CreateRow(4);
             datarow.CreateCell(0).SetCellValue(pagelist.model.InstituNum == null ? "" : pagelist.model.InstituNum.ToString());
             datarow.CreateCell(1).SetCellValue(pagelist.model.InstituNum_City == null ? "" : pagelist.model.InstituNum_City.ToString());
             datarow.CreateCell(2).SetCellValue(pagelist.model.InstituNum_Area == null ? "" : pagelist.model.InstituNum_Area.ToString());
             datarow.CreateCell(3).SetCellValue(pagelist.model.InstituNum_town == null ? "" : pagelist.model.InstituNum_town.ToString());
             datarow.CreateCell(4).SetCellValue(pagelist.model.PeopleNum == null ? "" : pagelist.model.PeopleNum.ToString());
             datarow.CreateCell(5).SetCellValue(pagelist.model.PeopleNum_City == null ? "" : pagelist.model.PeopleNum_City.ToString());
             datarow.CreateCell(6).SetCellValue(pagelist.model.PeopleNum_County == null ? "" : pagelist.model.PeopleNum_County.ToString());
             datarow.CreateCell(7).SetCellValue(pagelist.model.PeopleNum_man == null ? "" : pagelist.model.PeopleNum_man.ToString());
             datarow.CreateCell(8).SetCellValue(pagelist.model.PeopleNum_woman == null ? "" : pagelist.model.PeopleNum_woman.ToString());
             datarow.CreateCell(9).SetCellValue(pagelist.model.PeopleNum_age1 == null ? "" : pagelist.model.PeopleNum_age1.ToString());
             datarow.CreateCell(10).SetCellValue(pagelist.model.PeopleNum_age2 == null ? "" : pagelist.model.PeopleNum_age2.ToString());
             datarow.CreateCell(11).SetCellValue(pagelist.model.PeopleNum_age3 == null ? "" : pagelist.model.PeopleNum_age3.ToString());
             datarow.CreateCell(12).SetCellValue(pagelist.model.PeopleNum_age4 == null ? "" : pagelist.model.PeopleNum_age4.ToString());
             datarow.CreateCell(13).SetCellValue(pagelist.model.PeopleNum_xlss == null ? "" : pagelist.model.PeopleNum_xlss.ToString());
             datarow.CreateCell(14).SetCellValue(pagelist.model.PeopleNum_xlbk == null ? "" : pagelist.model.PeopleNum_xlbk.ToString());
             datarow.CreateCell(15).SetCellValue(pagelist.model.PeopleNum_xldz == null ? "" : pagelist.model.PeopleNum_xldz.ToString());
             datarow.CreateCell(16).SetCellValue(pagelist.model.PeopleNum_xlzz == null ? "" : pagelist.model.PeopleNum_xlzz.ToString());
             datarow.CreateCell(17).SetCellValue(pagelist.model.PeopleNum_xlqt == null ? "" : pagelist.model.PeopleNum_xlqt.ToString());
             datarow.CreateCell(18).SetCellValue(pagelist.model.PeopleNum_zczgj == null ? "" : pagelist.model.PeopleNum_zczgj.ToString());
             datarow.CreateCell(19).SetCellValue(pagelist.model.PeopleNum_zcfgj == null ? "" : pagelist.model.PeopleNum_zcfgj.ToString());
             datarow.CreateCell(20).SetCellValue(pagelist.model.PeopleNum_zczj == null ? "" : pagelist.model.PeopleNum_zczj.ToString());
             datarow.CreateCell(21).SetCellValue(pagelist.model.PeopleNum_zccj == null ? "" : pagelist.model.PeopleNum_zccj.ToString());
             datarow.CreateCell(22).SetCellValue(pagelist.model.PeopleNum_zcqt == null ? "" : pagelist.model.PeopleNum_zcqt.ToString());
             datarow.GetCell(0).CellStyle = style;
             datarow.GetCell(1).CellStyle = style;
             datarow.GetCell(2).CellStyle = style;
             datarow.GetCell(3).CellStyle = style;
             datarow.GetCell(4).CellStyle = style;
             datarow.GetCell(5).CellStyle = style;
             datarow.GetCell(6).CellStyle = style;
             datarow.GetCell(7).CellStyle = style;
             datarow.GetCell(8).CellStyle = style;
             datarow.GetCell(9).CellStyle = style;
             datarow.GetCell(10).CellStyle = style;
             datarow.GetCell(11).CellStyle = style;
             datarow.GetCell(12).CellStyle = style;
             datarow.GetCell(13).CellStyle = style;
             datarow.GetCell(14).CellStyle = style;
             datarow.GetCell(15).CellStyle = style;
             datarow.GetCell(16).CellStyle = style;
             datarow.GetCell(17).CellStyle = style;
             datarow.GetCell(18).CellStyle = style;
             datarow.GetCell(19).CellStyle = style;
             datarow.GetCell(20).CellStyle = style;
             datarow.GetCell(21).CellStyle = style;
             datarow.GetCell(22).CellStyle = style;*/

            sheet.CopyTo(workbook, "Sheet1", true, true);
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }

        /// <summary>
        /// 导出下载响应
        /// </summary>
        /// <param name="ReportCode"></param>
        public void GetExcelData(string ReportCode, string FillInYear, string AreaCode)
        {
            AreaServices areaServices = new AreaServices();
            string AreaName = areaServices.GetByCodeModel(AreaCode).model.Name;
            VegNowModel model = new VegNowModel();
            model.AreaCode = AreaCode;
            model.FillInYear = FillInYear;
            model.ReportCode = ReportCode;
            //打开完成excel文件并下载
            HttpContext curContext = HttpContext.Current;
            // 设置编码和附件格式
            curContext.Response.AddHeader("Access-Control-Allow-Origin", "*");
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            //curContext.Response.Charset = "GB2312";//防止中文出现乱码
            //System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);//防止中文出现乱码
            //curContext.Response.ContentEncoding = Encoding.GetEncoding("GB2312");  //设置输出流为简体中文 
            string strFileName = "（五）" + model.FillInYear + "-" + AreaName + "-水果技术推广机构及人员情况表.xls";
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
            //curContext.Response.AppendHeader("Content-Disposition",
            //   "attachment;filename=" + strFileName);
            curContext.Response.BinaryWrite(Method(model).GetBuffer());
            curContext.Response.End();
        }

        /// <summary>
        /// 静态表导入数据
        /// </summary>
        /// <param name="ReportCode"></param>
        /// <param name="AreaCode"></param>
        /// <returns></returns>
        public ResResult<FruTecPromotionModel> PostImportData(string FillInYear, string ReportCode, string AreaCode)
        {
            ResResult<FruTecPromotionModel> obj = new ResResult<FruTecPromotionModel>();
            FruTecPromotionModel pageModel = new FruTecPromotionModel();
            DictServices dictServices = new DictServices();
            ResResult<int> SingleObj = new ResResult<int>();
            HttpContext curContext = HttpContext.Current;
            HttpFileCollection files = curContext.Request.Files;
            if (curContext.Request.Files.Count != 0)
            {
                HttpPostedFile upfile = files[0];
                if (upfile != null && upfile.ContentLength > 0)
                {
                    string temppath = curContext.Server.MapPath("~/App_Data/temppath");
                    if (!Directory.Exists(temppath))//判断文件夹是否存在 
                    {
                        Directory.CreateDirectory(temppath);//不存在则创建文件夹 
                    }
                    string filetype = Path.GetExtension(upfile.FileName);
                    string filenewName = Guid.NewGuid().ToString().Replace("-", "") + filetype;
                    string strpath = Path.Combine(temppath, filenewName);
                    upfile.SaveAs(strpath);

                    string year = dictServices.GetKey(FillInYear);
                    if (fruTecPromotionServices.CheckRepCreate(ReportCode, AreaCode, year))
                    {
                        obj.status = -1;
                        obj.message = FillInYear + "的报表没有生成，请先行生成！！";
                        obj.model = pageModel;
                    }
                    else
                    {
                        bool isCity;  //是否是州市级                   
                        if (AreaCode.Substring(4, 8) == "00000000")
                        {
                           isCity = true;
                        }
                        else
                        {
                           isCity = false;
                        }
                        string ReportId = fruTecPromotionServices.GetReportId(ReportCode, AreaCode, year);
                        pageModel.ReportId = ReportId;
                        IWorkbook workbook = null;
                        ISheet sheet = null;
                        // FruTecPromotionModel pageModel = new FruTecPromotionModel();
                        FileStream fs = new FileStream(strpath, FileMode.Open, FileAccess.Read);
                        switch (filetype)
                        {
                            case ".xls":
                                workbook = new HSSFWorkbook(fs);
                                break;
                            case ".xlsx":
                                workbook = new XSSFWorkbook(fs);
                                break;
                            default:
                                workbook = new HSSFWorkbook(fs);
                                break;
                        }
                        sheet = workbook.GetSheetAt(0);
                        HSSFRow datarow1 = (HSSFRow)sheet.GetRow(4);
                        if (datarow1.GetCell(2) != null)
                        {
                            if (datarow1.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.InstituNum = (int)(datarow1.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow1.GetCell(2).ToString()))
                                {
                                    pageModel.InstituNum = int.Parse(datarow1.GetCell(2).ToString());
                                }

                            }
                        }



                        if (isCity)
                        {
                            HSSFRow datarow2 = (HSSFRow)sheet.GetRow(5);
                            if (datarow2.GetCell(2) != null)
                            {
                                if (datarow2.GetCell(2).CellType == CellType.Formula)
                                {
                                    pageModel.InstituNum_City = (int)(datarow2.GetCell(2).NumericCellValue);
                                }
                                else
                                {
                                    if (DataCheck.isNumber(datarow2.GetCell(2).ToString()))
                                    {
                                        pageModel.InstituNum_City = int.Parse(datarow2.GetCell(2).ToString());
                                    }
                                }
                            }
                        }


                        if (!isCity)
                        {
                            HSSFRow datarow3 = (HSSFRow)sheet.GetRow(6);
                            if (datarow3.GetCell(2) != null)
                            {
                                if (datarow3.GetCell(2).CellType == CellType.Formula)
                                {
                                    pageModel.InstituNum_Area = (int)(datarow3.GetCell(2).NumericCellValue);
                                }
                                else
                                {
                                    if (DataCheck.isNumber(datarow3.GetCell(2).ToString()))
                                    {
                                        pageModel.InstituNum_Area = int.Parse(datarow3.GetCell(2).ToString());
                                    }

                                }
                            }



                            HSSFRow datarow4 = (HSSFRow)sheet.GetRow(7);
                            if (datarow4.GetCell(2) != null)
                            {
                                if (datarow4.GetCell(2).CellType == CellType.Formula)
                                {
                                    pageModel.InstituNum_town = (int)(datarow4.GetCell(2).NumericCellValue);
                                }
                                else
                                {
                                    if (DataCheck.isNumber(datarow4.GetCell(2).ToString()))
                                    {
                                        pageModel.InstituNum_town = int.Parse(datarow4.GetCell(2).ToString());
                                    }

                                }
                            }
                        }


                        HSSFRow datarow5 = (HSSFRow)sheet.GetRow(8);
                        if (datarow5.GetCell(2) != null)
                        {
                            if (datarow5.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum = (int)(datarow5.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow5.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum = int.Parse(datarow5.GetCell(2).ToString());
                                }

                            }
                        }



                        if (!isCity)
                        {
                            HSSFRow datarow6 = (HSSFRow)sheet.GetRow(9);
                            if (datarow6.GetCell(2) != null)
                            {
                                if (datarow6.GetCell(2).CellType == CellType.Formula)
                                {
                                    pageModel.PeopleNum_County = (int)(datarow6.GetCell(2).NumericCellValue);
                                }
                                else
                                {
                                    if (DataCheck.isNumber(datarow6.GetCell(2).ToString()))
                                    {
                                        pageModel.PeopleNum_County = int.Parse(datarow6.GetCell(2).ToString());
                                    }

                                }
                            }
                        }


                        if (isCity)
                        {
                            HSSFRow datarow7 = (HSSFRow)sheet.GetRow(10);
                            if (datarow7.GetCell(2) != null)
                            {
                                if (datarow7.GetCell(2).CellType == CellType.Formula)
                                {
                                    pageModel.PeopleNum_City = (int)(datarow7.GetCell(2).NumericCellValue);
                                }
                                else
                                {
                                    if (DataCheck.isNumber(datarow7.GetCell(2).ToString()))
                                    {
                                        pageModel.PeopleNum_City = int.Parse(datarow7.GetCell(2).ToString());
                                    }

                                }
                            }
                        }



                        HSSFRow datarow8 = (HSSFRow)sheet.GetRow(11);
                        if (datarow8.GetCell(2) != null)
                        {
                            if (datarow8.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_man = (int)(datarow8.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow8.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_man = int.Parse(datarow8.GetCell(2).ToString());
                                }

                            }
                        }
                       



                        HSSFRow datarow9 = (HSSFRow)sheet.GetRow(12);
                        if (datarow9.GetCell(2) != null)
                        {
                            if (datarow9.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_woman = (int)(datarow9.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow9.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_woman = int.Parse(datarow9.GetCell(2).ToString());
                                }

                            }
                        }
                        



                        HSSFRow datarow10 = (HSSFRow)sheet.GetRow(14);
                        if (datarow10.GetCell(2) != null)
                        {
                            if (datarow10.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_age1 = (int)(datarow10.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow10.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_age1 = int.Parse(datarow10.GetCell(2).ToString());
                                }

                            }

                        }



                        HSSFRow datarow11 = (HSSFRow)sheet.GetRow(15);
                        if (datarow11.GetCell(2) != null)
                        {
                            if (datarow11.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_age2 = (int)(datarow11.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow11.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_age2 = int.Parse(datarow11.GetCell(2).ToString());
                                }

                            }
                        }
                       



                        HSSFRow datarow12 = (HSSFRow)sheet.GetRow(16);
                        if (datarow12.GetCell(2) != null)
                        {
                            if (datarow12.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_age3 = (int)(datarow12.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow12.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_age3 = int.Parse(datarow12.GetCell(2).ToString());
                                }

                            }
                        }
                       



                        HSSFRow datarow13 = (HSSFRow)sheet.GetRow(17);
                        if (datarow13.GetCell(2) != null)
                        {
                            if (datarow13.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_age4 = (int)(datarow13.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow13.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_age4 = int.Parse(datarow13.GetCell(2).ToString());
                                }

                            }
                        }
                       



                        HSSFRow datarow14 = (HSSFRow)sheet.GetRow(19);
                        if (datarow14.GetCell(2) != null)
                        {
                            if (datarow14.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_xlss = (int)(datarow14.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow14.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_xlss = int.Parse(datarow14.GetCell(2).ToString());
                                }

                            }
                        }
                       



                        HSSFRow datarow15 = (HSSFRow)sheet.GetRow(20);
                        if (datarow15.GetCell(2) != null)
                        {
                            if (datarow15.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_xlbk = (int)(datarow15.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow15.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_xlbk = int.Parse(datarow15.GetCell(2).ToString());
                                }

                            }
                        }
                       



                        HSSFRow datarow16 = (HSSFRow)sheet.GetRow(21);
                        if (datarow16.GetCell(2) != null)
                        {
                            if (datarow16.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_xldz = (int)(datarow16.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow16.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_xldz = int.Parse(datarow16.GetCell(2).ToString());
                                }

                            }
                        }
                        



                        HSSFRow datarow17 = (HSSFRow)sheet.GetRow(22);
                        if (datarow17.GetCell(2) != null)
                        {
                            if (datarow17.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_xlzz = (int)(datarow17.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow17.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_xlzz = int.Parse(datarow17.GetCell(2).ToString());
                                }

                            }
                        }
                       



                        HSSFRow datarow18 = (HSSFRow)sheet.GetRow(23);
                        if (datarow18.GetCell(2) != null)
                        {
                            if (datarow18.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_xlqt = (int)(datarow18.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow18.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_xlqt = int.Parse(datarow18.GetCell(2).ToString());
                                }

                            }
                        }
                       



                        HSSFRow datarow19 = (HSSFRow)sheet.GetRow(25);
                        if (datarow19.GetCell(2) != null)
                        {
                            if (datarow19.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_zczgj = (int)(datarow19.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow19.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_zczgj = int.Parse(datarow19.GetCell(2).ToString());
                                }

                            }
                        }
                       



                        HSSFRow datarow20 = (HSSFRow)sheet.GetRow(26);
                        if (datarow20.GetCell(2) != null)
                        {
                            if (datarow20.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_zcfgj = (int)(datarow20.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow20.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_zcfgj = int.Parse(datarow20.GetCell(2).ToString());
                                }

                            }
                        }
                       



                        HSSFRow datarow21 = (HSSFRow)sheet.GetRow(27);
                        if (datarow21.GetCell(2) != null)
                        {
                            if (datarow21.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_zczj = (int)(datarow21.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow21.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_zczj = int.Parse(datarow21.GetCell(2).ToString());
                                }

                            }
                        }
                       



                        HSSFRow datarow22 = (HSSFRow)sheet.GetRow(28);
                        if (datarow22.GetCell(2) != null)
                        {
                            if (datarow22.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_zccj = (int)(datarow22.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow22.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_zccj = int.Parse(datarow22.GetCell(2).ToString());
                                }

                            }
                        }
                       



                        HSSFRow datarow23 = (HSSFRow)sheet.GetRow(29);
                        if (datarow23.GetCell(2) != null )
                        {
                            if (datarow23.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_zcqt = (int)(datarow23.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow23.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_zcqt = int.Parse(datarow23.GetCell(2).ToString());
                                }

                            }
                        }
                        //公式
                        int tempInstituNum = 0;
                        if (pageModel.InstituNum_Area != null)
                        {
                            tempInstituNum += (int)pageModel.InstituNum_Area;
                        }
                        if (pageModel.InstituNum_City != null)
                        {
                            tempInstituNum += (int)pageModel.InstituNum_City;
                        }
                        if (pageModel.InstituNum_town != null)
                        {
                            tempInstituNum += (int)pageModel.InstituNum_town;
                        }
                        if (tempInstituNum != 0)
                        {
                            pageModel.InstituNum = tempInstituNum;
                        }
                        //按年龄分
                        int tempPeopleNum = 0;
                        if(pageModel .PeopleNum_age1 != null)
                        {
                            tempPeopleNum += (int)pageModel.PeopleNum_age1;
                        }
                        if (pageModel.PeopleNum_age2 != null)
                        {
                            tempPeopleNum += (int)pageModel.PeopleNum_age2;
                        }
                        if (pageModel.PeopleNum_age3 != null)
                        {
                            tempPeopleNum += (int)pageModel.PeopleNum_age3;
                        }
                        if (pageModel.PeopleNum_age4 != null)
                        {
                            tempPeopleNum += (int)pageModel.PeopleNum_age4;
                        }
                       

                        //按州市、县区分
                        int PeopleNum1 = 0;
                        if(pageModel.PeopleNum_City != null)
                        {
                            PeopleNum1 += (int)pageModel.PeopleNum_City;
                        }
                        if(pageModel.PeopleNum_County != null)
                        {
                            PeopleNum1 += (int)pageModel.PeopleNum_County;
                        }

                        //按性别分
                        int PeopleNum2 = 0;
                        if(pageModel.PeopleNum_man != null)
                        {
                            PeopleNum2 += (int)pageModel.PeopleNum_man;
                        }
                        if (pageModel.PeopleNum_woman != null)
                        {
                            PeopleNum2 += (int)pageModel.PeopleNum_woman;
                        }

                        //按学历层次分
                        int PeopleNum3 = 0;
                        if(pageModel.PeopleNum_xlbk != null)//本科
                        {
                            PeopleNum3 += (int)pageModel.PeopleNum_xlbk;
                        }
                        if (pageModel.PeopleNum_xldz != null)//大专
                        {
                            PeopleNum3 += (int)pageModel.PeopleNum_xldz;
                        }
                        if (pageModel.PeopleNum_xlqt != null)//其他
                        {
                            PeopleNum3 += (int)pageModel.PeopleNum_xlqt;
                        }
                        if (pageModel.PeopleNum_xlss != null)//硕士
                        {
                            PeopleNum3 += (int)pageModel.PeopleNum_xlss;
                        }
                        if (pageModel.PeopleNum_xlzz != null)//中专
                        {
                            PeopleNum3 += (int)pageModel.PeopleNum_xlzz;
                        }

                        //按职称分
                        int PeopleNum4 = 0;
                        if(pageModel.PeopleNum_zccj != null)//初级职称
                        {
                            PeopleNum4 += (int)pageModel.PeopleNum_zccj;
                        }
                        if (pageModel.PeopleNum_zcfgj != null)//副高级
                        {
                            PeopleNum4 += (int)pageModel.PeopleNum_zcfgj;
                        }
                        if (pageModel.PeopleNum_zcqt != null)//其他
                        {
                            PeopleNum4 += (int)pageModel.PeopleNum_zcqt;
                        }
                        if (pageModel.PeopleNum_zczgj != null)//正高级
                        {
                            PeopleNum4 += (int)pageModel.PeopleNum_zczgj;
                        }
                        if (pageModel.PeopleNum_zczj != null)//中级
                        {
                            PeopleNum4 += (int)pageModel.PeopleNum_zczj;
                        }
                        if (pageModel.PeopleNum != tempPeopleNum)
                        {
                            obj.status = DefaultKey.ErrorResult;
                            obj.message = "总人数不等于年龄子项之和！";
                            obj.model = null;
                            return obj;
                        }
                        if (pageModel.PeopleNum != PeopleNum1)
                        {
                            obj.status = DefaultKey.ErrorResult;
                            obj.message = "总人数不等于区域子项之和！";
                            obj.model = null;
                            return obj;
                        }
                        if (pageModel.PeopleNum != PeopleNum2)
                        {
                            obj.status = DefaultKey.ErrorResult;
                            obj.message = "总人数不等于性别子项之和！";
                            obj.model = null;
                            return obj;
                        }
                        if (pageModel.PeopleNum != PeopleNum3)
                        {
                            obj.status = DefaultKey.ErrorResult;
                            obj.message = "总人数不等于学历层次子项之和！";
                            obj.model = null;
                            return obj;
                        }
                        if (pageModel.PeopleNum != PeopleNum4)
                        {
                            obj.status = DefaultKey.ErrorResult;
                            obj.message = "总人数不等于职称子项之和！";
                            obj.model = null;
                            return obj;
                        }

                        //判断list中是否初始化过报表
                        if (fruTecPromotionServices.CheckRepCount(ReportId))
                        {
                            //没初始化过进来
                            fruTecPromotionServices.AddInfo(pageModel);
                        }
                        else
                        {
                            fruTecPromotionServices.UpdateInfo(pageModel);
                        }

                        obj.status = 1;
                        obj.message = "";
                        obj.model = pageModel;
                    }




                    Directory.Delete(temppath, true);
                }
            }
            return obj;
        }

        public ResResult<List<PromotionStatisModel>> GetStatisListData(string AreaCode, string FillInYear)
        {
            return fruTecPromotionServices.getStatisListData(AreaCode, FillInYear);
        }

        public void GetStatisExcel(string AreaCode, string FillInYear)
        {
            AreaServices areaServices = new AreaServices();
            string AreaName = areaServices.GetByCodeModel(AreaCode).model.Name;
            //打开完成excel文件并下载
            HttpContext curContext = HttpContext.Current;
            // 设置编码和附件格式
            curContext.Response.AddHeader("Access-Control-Allow-Origin", "*");
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            string strFileName = FillInYear + "年-" + AreaName + "-水果服务机构人员情况汇总表.xls";
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
            //curContext.Response.AppendHeader("Content-Disposition",
            //   "attachment;filename=" + strFileName);
            curContext.Response.BinaryWrite(loadStatis(AreaCode, FillInYear).GetBuffer());
            curContext.Response.End();
        }

        public MemoryStream loadStatis(string AreaCode, string FillInYear)
        {
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\FruStatisDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet tempsheet = templateBook.GetSheet("FruTecPromotion") as HSSFSheet;//读取模板所在sheet位置
            List<PromotionStatisModel> pagelist = (List<PromotionStatisModel>)fruTecPromotionServices.getStatisListData(AreaCode, FillInYear).model;//数据获取

            HSSFRow row = (HSSFRow)tempsheet.GetRow(4);//读取模板第5行
            var style = row.GetCell(1).CellStyle;//读取模板第5行第2单元格样式并保存
            row.GetCell(1).CellStyle = style;//对第3行第2单元格设置原来样式

            HSSFSheet sheet = CopyToSheet(tempsheet, pagelist);
            //if (pagelist.Count > 0)
            //{
            //    for (int i = 0; i < pagelist.Count; i++)
            //    {
            //        HSSFRow datarow = (HSSFRow)sheet.CreateRow(4 + i);
            //        datarow.CreateCell(0).SetCellValue(pagelist[i].AreaName == null ? "" : pagelist[i].AreaName);
            //        datarow.CreateCell(1).SetCellValue(pagelist[i].InstituNum == null ? "" : pagelist[i].InstituNum.ToString());
            //        datarow.CreateCell(2).SetCellValue(pagelist[i].InstituNum_City == null ? "" : pagelist[i].InstituNum_City.ToString());
            //        datarow.CreateCell(3).SetCellValue(pagelist[i].InstituNum_Area == null ? "" : pagelist[i].InstituNum_Area.ToString());
            //        datarow.CreateCell(4).SetCellValue(pagelist[i].InstituNum_town == null ? "" : pagelist[i].InstituNum_town.ToString());
            //        datarow.CreateCell(5).SetCellValue(pagelist[i].PeopleNum == null ? "" : pagelist[i].PeopleNum.ToString());
            //        datarow.CreateCell(6).SetCellValue(pagelist[i].PeopleNum_County == null ? "" : pagelist[i].PeopleNum_County.ToString());
            //        datarow.CreateCell(7).SetCellValue(pagelist[i].PeopleNum_City == null ? "" : pagelist[i].PeopleNum_City.ToString());
            //        datarow.CreateCell(8).SetCellValue(pagelist[i].PeopleNum_man == null ? "" : pagelist[i].PeopleNum_man.ToString());
            //        datarow.CreateCell(9).SetCellValue(pagelist[i].PeopleNum_woman == null ? "" : pagelist[i].PeopleNum_woman.ToString());
            //        datarow.CreateCell(10).SetCellValue(pagelist[i].PeopleNum_age1 == null ? "" : pagelist[i].PeopleNum_age1.ToString());
            //        datarow.CreateCell(11).SetCellValue(pagelist[i].PeopleNum_age2 == null ? "" : pagelist[i].PeopleNum_age2.ToString());
            //        datarow.CreateCell(12).SetCellValue(pagelist[i].PeopleNum_age3 == null ? "" : pagelist[i].PeopleNum_age3.ToString());
            //        datarow.CreateCell(13).SetCellValue(pagelist[i].PeopleNum_age4 == null ? "" : pagelist[i].PeopleNum_age4.ToString());
            //        datarow.CreateCell(14).SetCellValue(pagelist[i].PeopleNum_xlss == null ? "" : pagelist[i].PeopleNum_xlss.ToString());
            //        datarow.CreateCell(15).SetCellValue(pagelist[i].PeopleNum_xlbk == null ? "" : pagelist[i].PeopleNum_xlbk.ToString());
            //        datarow.CreateCell(16).SetCellValue(pagelist[i].PeopleNum_xldz == null ? "" : pagelist[i].PeopleNum_xldz.ToString());
            //        datarow.CreateCell(17).SetCellValue(pagelist[i].PeopleNum_xlzz == null ? "" : pagelist[i].PeopleNum_xlzz.ToString());
            //        datarow.CreateCell(18).SetCellValue(pagelist[i].PeopleNum_xlqt == null ? "" : pagelist[i].PeopleNum_xlqt.ToString());
            //        datarow.CreateCell(19).SetCellValue(pagelist[i].PeopleNum_zczgj == null ? "" : pagelist[i].PeopleNum_zczgj.ToString());
            //        datarow.CreateCell(20).SetCellValue(pagelist[i].PeopleNum_zcfgj == null ? "" : pagelist[i].PeopleNum_zcfgj.ToString());
            //        datarow.CreateCell(21).SetCellValue(pagelist[i].PeopleNum_zczj == null ? "" : pagelist[i].PeopleNum_zczj.ToString());
            //        datarow.CreateCell(22).SetCellValue(pagelist[i].PeopleNum_zccj == null ? "" : pagelist[i].PeopleNum_zccj.ToString());
            //        datarow.CreateCell(23).SetCellValue(pagelist[i].PeopleNum_zcqt == null ? "" : pagelist[i].PeopleNum_zcqt.ToString());

            //        for (int j = 0; j <= 23; j++)
            //        {
            //            datarow.GetCell(j).CellStyle = style;
            //        }

            //    }
            //}
            AreaServices areaServices = new AreaServices();
            string areaName = areaServices.GetByCodeModel(AreaCode).model.Name;
            sheet.CopyTo(workbook, areaName, true, true);

            if (AreaCode.Equals("530000000000"))
            {
                var citys = areaServices.GetPrefectureList();
                foreach (string areacode in citys)
                {
                    List<PromotionStatisModel> citylist = fruTecPromotionServices.getStatisList(areacode, FillInYear);
                    FileStream fm = new FileStream(templateName, FileMode.Open, FileAccess.Read);
                    HSSFWorkbook Book = new HSSFWorkbook(fm);
                    HSSFSheet temp = Book.GetSheet("FruTecPromotion") as HSSFSheet;
                    HSSFRow row1 = (HSSFRow)temp.GetRow(4);
                    HSSFSheet sheet1 = CopyToSheet(temp, citylist);
                    string areaName1 = areaServices.GetByCodeModel(areacode).model.Name;
                    sheet1.CopyTo(workbook, areaName1, true, true);
                }
            }

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }

        public HSSFSheet CopyToSheet(HSSFSheet sheet, List<PromotionStatisModel> list)
        {
            HSSFRow row = (HSSFRow)sheet.GetRow(4);
            if (list.Count > 0)
            {
                for (int i = 0; i < list.Count; i++)
                {
                    HSSFRow datarow = (HSSFRow)sheet.CreateRow(4 + i);
                    datarow.CreateCell(0).SetCellValue(list[i].AreaName == null ? "" : list[i].AreaName);
                    datarow.CreateCell(1).SetCellValue(list[i].InstituNum == null ? "" : list[i].InstituNum.ToString());
                    datarow.CreateCell(2).SetCellValue(list[i].InstituNum_City == null ? "" : list[i].InstituNum_City.ToString());
                    datarow.CreateCell(3).SetCellValue(list[i].InstituNum_Area == null ? "" : list[i].InstituNum_Area.ToString());
                    datarow.CreateCell(4).SetCellValue(list[i].InstituNum_town == null ? "" : list[i].InstituNum_town.ToString());
                    datarow.CreateCell(5).SetCellValue(list[i].PeopleNum == null ? "" : list[i].PeopleNum.ToString());
                    datarow.CreateCell(6).SetCellValue(list[i].PeopleNum_County == null ? "" : list[i].PeopleNum_County.ToString());
                    datarow.CreateCell(7).SetCellValue(list[i].PeopleNum_City == null ? "" : list[i].PeopleNum_City.ToString());
                    datarow.CreateCell(8).SetCellValue(list[i].PeopleNum_man == null ? "" : list[i].PeopleNum_man.ToString());
                    datarow.CreateCell(9).SetCellValue(list[i].PeopleNum_woman == null ? "" : list[i].PeopleNum_woman.ToString());
                    datarow.CreateCell(10).SetCellValue(list[i].PeopleNum_age1 == null ? "" : list[i].PeopleNum_age1.ToString());
                    datarow.CreateCell(11).SetCellValue(list[i].PeopleNum_age2 == null ? "" : list[i].PeopleNum_age2.ToString());
                    datarow.CreateCell(12).SetCellValue(list[i].PeopleNum_age3 == null ? "" : list[i].PeopleNum_age3.ToString());
                    datarow.CreateCell(13).SetCellValue(list[i].PeopleNum_age4 == null ? "" : list[i].PeopleNum_age4.ToString());
                    datarow.CreateCell(14).SetCellValue(list[i].PeopleNum_xlss == null ? "" : list[i].PeopleNum_xlss.ToString());
                    datarow.CreateCell(15).SetCellValue(list[i].PeopleNum_xlbk == null ? "" : list[i].PeopleNum_xlbk.ToString());
                    datarow.CreateCell(16).SetCellValue(list[i].PeopleNum_xldz == null ? "" : list[i].PeopleNum_xldz.ToString());
                    datarow.CreateCell(17).SetCellValue(list[i].PeopleNum_xlzz == null ? "" : list[i].PeopleNum_xlzz.ToString());
                    datarow.CreateCell(18).SetCellValue(list[i].PeopleNum_xlqt == null ? "" : list[i].PeopleNum_xlqt.ToString());
                    datarow.CreateCell(19).SetCellValue(list[i].PeopleNum_zczgj == null ? "" : list[i].PeopleNum_zczgj.ToString());
                    datarow.CreateCell(20).SetCellValue(list[i].PeopleNum_zcfgj == null ? "" : list[i].PeopleNum_zcfgj.ToString());
                    datarow.CreateCell(21).SetCellValue(list[i].PeopleNum_zczj == null ? "" : list[i].PeopleNum_zczj.ToString());
                    datarow.CreateCell(22).SetCellValue(list[i].PeopleNum_zccj == null ? "" : list[i].PeopleNum_zccj.ToString());
                    datarow.CreateCell(23).SetCellValue(list[i].PeopleNum_zcqt == null ? "" : list[i].PeopleNum_zcqt.ToString());

                    for (int j = 0; j <= 23; j++)
                    {
                        datarow.GetCell(j).CellStyle = row.GetCell(j).CellStyle;
                    }

                }
            }
            return sheet;
        }

    }
}